package vsqlbuilder.logic;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import vsqlbuilder.logic.postgresql.connection;

public class LogicModel {

	public List<Schema> schemas;
	public connection DatabaseConnection; // TODO [low]: put private (if
											// possible but probably not)

	public LogicModel() {
		// TODO [low]: eliminate possible Critical bug in future: when a sql
		// setences return 0 rows validate this situation
		this.schemas = new ArrayList<Schema>();
	}

	// TODO [High]: return correct value & validate to display table only with
	// user access to select
	public boolean reverseingDB() {
		boolean value = true;
		// TODO [low]: change this way of doing reverse ing. with temp
		// connection (using 2 connections) for a pool of connections
		connection TempDBConnection = new connection(DatabaseConnection
				.getHostName(), DatabaseConnection.getDatabaseName(),
				DatabaseConnection.getPort(), DatabaseConnection.getUser(),
				DatabaseConnection.getPassword());
		// if the database connection data is set but is not open, then open it.
		if (!this.DatabaseConnection.isOpenConnection()
				&& this.DatabaseConnection.getPassword().length() > 0) {
			this.DatabaseConnection.openConnection();
			// TODO [medium]: this is a mistake because if is not open just open
			// it, but nothing more...
		} else // else if is open used it, if not open then no use it.
		if (this.DatabaseConnection.isOpenConnection()) {
			String sql = "SELECT nspname from pg_catalog.pg_namespace where nspname not in ('pg_catalog', 'pg_toast', 'information_schema') and nspname not like 'pg_temp%'";
			if (this.DatabaseConnection.execSQL(sql)) {
				try {
					while (this.DatabaseConnection.resultsetDB.next()) {
						this.schemas.add(new Schema(new String(
								this.DatabaseConnection.resultsetDB
										.getString(1))));
					}
				} catch (SQLException se) {
					value = false;
					System.out
							.println("We got an exception while creating reverse ingenieering db "
									+ "shouldn't happen: we've done something really bad.");
					se.printStackTrace();
					System.exit(1);
				}
			}
			this.DatabaseConnection.closeSQL();

			TempDBConnection.openConnection(); // to use the get structure of
												// table objects
			for (Schema s : this.schemas) {
				// Select table for every schema
				Table tabletemp = null;
				/*sql = "SELECT c.relname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE n.oid = c.relnamespace AND c.relkind = 'r' AND pg_catalog.pg_table_is_visible(c.oid) and n.nspname not in ('pg_catalog') AND n.nspname='"
						+ s.getName() + "' ORDER BY 1";*/
				sql = "SELECT DISTINCT table_name FROM information_schema.columns WHERE table_schema='"
						+ s.getName() + "' ORDER BY 1";						
				if (this.DatabaseConnection.execSQL(sql)) {
					try {
						while (this.DatabaseConnection.resultsetDB.next()) {
							tabletemp = new Table(new String(
									this.DatabaseConnection.resultsetDB
											.getString(1)), s);
							s.addTable(tabletemp);
							tabletemp.getTableStructure(TempDBConnection);
						}
					} catch (SQLException se) {
						value = false;
						System.out
								.println("We got an exception while creating reverse ingenieering db "
										+ "shouldn't happen: we've done something really bad.");
						se.printStackTrace();
						System.exit(1);
					}
				}
				this.DatabaseConnection.closeSQL();
			}
			TempDBConnection.closeConnection();
		}

		return value;
	}

	public void emptyDatabaseModel() {
		for (Schema s : this.schemas) {
			for (Table t : s.tables) {
				t.columns.clear();
			}
			s.tables.clear();
		}
		this.schemas.clear();
	}

}
